<?php
define('SYSTEM_ROOT', $_SERVER['DOCUMENT_ROOT'] . '/');

class  SQL
{
    /*/--------------------------------------------
       private static  $DB = array(
           "host"=>"localhost",	    //数据库地址
           "dbname"=>"dian",		//数据库名
           "user"=>"dian",			//数据库帐号
           "password"=>"1111111111"	//数据库密码
    );
    */
    protected static $_instance = null;
    protected $dbName = '';
    protected $dsn;
    protected $dbh;

    /**
     * 构造
     * @return PDO
     */
    private function __construct($dbHost, $dbName, $dbUser, $dbPasswd)
    {
        try {
            $this->dsn = 'mysql:host=' . $dbHost . ';dbname=' . $dbName;
            $this->dbh = new PDO($this->dsn, $dbUser, $dbPasswd, array(PDO::MYSQL_ATTR_INIT_COMMAND => "set names utf8"));
            $this->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        } catch (PDOException $e) {
            self::outErrorMsg($e->getMessage(), $e);
        }
    }

    /**
     * Singleton instance
     *
     * @return Object
     */
    public static function getInstance()
    {
        if (self::$_instance === null) {
            include(SYSTEM_ROOT . "config.php");
            self::$_instance = new self($DB['host'], $DB['dbname'], $DB['user'], $DB['password']);
        }
        return self::$_instance;
    }

    /**
     * 开放数据库名信息，写sql语句有时候需要数据库名，具体看个人需求
     * @return    string    返回数据库名
     */
    public static function getDBName()
    {
        include(SYSTEM_ROOT . "config.php");
        return $DB['dbname'];
    }

    /*
     * 数据读取方法,只需要传入sql查询语句即可
     * @param	string  $sqlStr sql	查询语句字符串
     * @param	string  $dataArray	查询的参数列表
     * @return	array	返回值是多维数组，具体看语句查询结果。
     * 例如：返回只有一条数据，则取$arr[0]["字段名字"],多条数据则$arr[1][]、$arr[2][]
     * 注意：如果查询不到数据,返回空数组,且数组长度是1,不是0
     */
    public static function Read($sqlStr, $dataArray)
    {
        try {
            $pdo = self::getInstance()->dbh;

            $stmt = $pdo->prepare($sqlStr);

            //执行预处理语句
            if ($dataArray != null && count($dataArray) > 0) {
                $stmt->execute($dataArray);
            } else {
                $stmt->execute();
            }

            //以数组形式返回
            $arr = $stmt->fetchAll(PDO::FETCH_ASSOC);
            return $arr;
        } catch (PDOException $e) {
            self::outErrorMsg($e->getMessage(), $e);
        }
    }

    /*
     * 数据插入、更新、删除方法
     * @param	string   $sqlStr  sql操作语句字符串
     * @param	array    $dataArray   数据参数通过数组方式传递
     * @return	bool	 true操作成功，false操作失败
     */
    public static function Write($sqlStr, $dataArray)
    {
        try {
            $pdo = self::getInstance()->dbh;

            $stmt = $pdo->prepare($sqlStr);

            //执行预处理语句
            if ($dataArray != null && count($dataArray) > 0) {
                $stmt->execute($dataArray);
            } else {
                $stmt->execute();
            }

            //获取影响行数
            $affect_row = $stmt->rowCount();

            if ($affect_row) {
                return true;
            } else {
                return false;
            }
        } catch (PDOException $e) {
            self::outErrorMsg($e->getMessage(), $e);
        }
    }

    /**
     * 批量数据更新-开启事物
     * @param $bathDataArray 二维数组
     * @return bool true or false
     * $bathDataArray[0][0]sql语句
     * $bathDataArray[0][1]sql语句对应的参数
     */
    public static function batchWrite($bathDataArray)
    {
        try {
            $pdo = self::getInstance()->dbh;
            //开启事物
            $pdo->beginTransaction();

            $dataArrayLenth = count($bathDataArray);
            for ($i = 0; $i < $dataArrayLenth; $i++) {
                //读取sql语句
                $stmt = $pdo->prepare($bathDataArray[$i][0]);

                //执行预处理语句
                $stmt->execute($bathDataArray[$i][1]);
            }

            //执行事物的提交操作
            $pdo->commit();

            //获取影响行数
            $affect_row = $stmt->rowCount();
            if ($affect_row) {
                return true;
            } else {
                return false;
            }
        } catch (PDOException $e) {
            $pdo->rollBack();
            self::outErrorMsg($e->getMessage(), $e);
        }
    }

    /**
     * 用于创建或者修改数据库表结构
     * @param $sqlArray 创建、修改表结构的sql语句
     * @return array    执行结果,成功条数,失败条数
     */
    public static function exec($sqlArray)
    {
        try {
            $pdo = SQL::getInstance()->dbh;
            $pdo->beginTransaction();
            $sqlLength = count($sqlArray);
            //执行状态
            $statusData = array();

            //失败条数
            $errorCount = 0;
            //失败信息数组
            $errorData = array();
            for ($i = 0; $i < $sqlLength; $i++) {
                if ($sql = trim($sqlArray[$i])) {
                    $pdo->exec($sql);
                    $code = $pdo->errorCode();
                    if ($code != 00000) {
                        $errorData[$errorCount][0] = $code;
                        $errorData[$errorCount][1] = $sql;
                        $errorCount++;
                    }
                }
            }

            $pdo->commit();
            $statusData = array("sucCount" => $i, "errorDate" => $errorData);
            return $statusData;
        } catch (PDOException $e) {
            //执行事物的回滚操作
            $pdo->rollBack();
            self::outErrorMsg($e->getMessage(), $e);
        }
    }

    /**
     * 输出错误信息
     * @param $msg
     * @param $object
     */
    private static function outErrorMsg($msg, $object)
    {
        die(json_encode(array('success' => false, 'msg' => $msg, 'data' => $object)));
    }
}

?>

